package com.bj58.mis.apm.server.kylin;

import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

public class KylinJDBC {

    private static int thread = 1;
    private static int loop = 10;

    Connection conn;

    public KylinJDBC(){
        try {
            Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
            Properties info = new Properties();
            info.put("user", "ADMIN");
            info.put("password", "KYLIN");
            conn = driver.connect("jdbc:kylin://10.126.89.86:7070/kylin_test", info);
//            conn = driver.connect("jdbc:kylin://10.126.89.86:7070/student", info);
//            conn = driver.connect("jdbc:kylin://10.126.89.86:7070/h_test", info);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public void exec() throws Exception{
        int i = 1;
        while(i <= loop) {

            //anylist();
            this.testQuery();
            System.out.println("=" +
                    "===============  " + i + "  " + Thread.currentThread().getName());
            Thread.sleep(10000);
            i++;
        }
    }

    public static void main(String[] args) throws Exception {
        //query();
//        String col = "SODAK_DKA.USER";
//        String[] cols = col.split("[.]");
//        System.out.println(cols.length);

        int j = 1;
        while(j <= thread){
            new Thread(){
                @Override
                public void run() {
                    try {
                        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Thread.currentThread().getName() + "is start");
                        new KylinJDBC().exec();
                        System.out.println("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<" + Thread.currentThread().getName() + "is end");
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
            }.start();
            j++;
        }

    }

    public static void t(){
        int total = 100;
        int step = 0;
        int index = 1;
        int max = 6;
        String exec = null;
        while(index <= 100){
            if(index%max < 3){
                if(step >= 0)
                    step += 1;
                else
                    step = 0;
                exec = "yes";
            }else{
                step -= 1;
                exec = "no";
            }
            total += step;
            System.out.println("day"+index+" : exec="+exec+", step="+step+", total="+total);
            index ++;
        }
    }
    public static void query() throws Exception {
        Driver driver =(Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
        Properties info= new Properties();
        info.put("user","ADMIN");
        info.put("password","KYLIN");
        Connection conn= driver.connect("jdbc:kylin://10.126.89.86:7070/student",info);
        PreparedStatement state = conn.prepareStatement("select s.subject_id, max(user_name) as userName,sum(s.score) as sumscore from ods.o_m_t_score s left join o_m_t_subject j on s.subject_id = j.id left join o_m_t_student d on s.user_id = d.id where d.city = ? group by s.subject_id");
        state.setString(1, "唐山");
        ResultSet resultSet = state.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("subject_id");
            Long sumscore = resultSet.getLong("sumscore");

            System.out.println(id+":"+sumscore);
        }
    }

    public static void anylist() throws Exception {
        Driver driver =(Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
        Properties info= new Properties();
        info.put("user","ADMIN");
        info.put("password","KYLIN");
        Connection conn= driver.connect("jdbc:kylin://10.126.89.86:7070/student",info);

        PreparedStatement state = conn.prepareStatement("select * from ODS.O_M_T_STUDENT order by id");
        ResultSet resultSet = state.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String username = resultSet.getString("user_name");
            String sex = resultSet.getString("sex");
            String province = resultSet.getString("province");
            String city = resultSet.getString("city");
            System.out.println(id+":"+username+":"+sex+":"+province+":"+city);
        }

        state = conn.prepareStatement("select * from ODS.O_M_T_SUBJECT order by id");
        resultSet = state.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String subjectname = resultSet.getString("subject_name");
            System.out.println(id+":"+subjectname);
        }

        state = conn.prepareStatement("select * from ODS.O_M_T_SCORE order by id");
        resultSet = state.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String userid = resultSet.getString("user_id");
            String subjectid = resultSet.getString("subject_id");
            String score = resultSet.getString("score");
            System.out.println(id+":"+userid+":"+subjectid+":"+score);
        }

        state = conn.prepareStatement("select j.id,j.subject_name,sum(s.score) as sumscore," +
                "max(s.score) as maxscore, min(s.score) as minscore,avg(s.score) as avgscore" +
                " from ods.o_m_t_score s left join o_m_t_subject j on s.subject_id = j.id group by j.id,j.subject_name");
        resultSet = state.executeQuery();
        while (resultSet.next()) {
            String subjectid = resultSet.getString("id");
            String subjectname = resultSet.getString("subject_name");
            String sumscore = resultSet.getString("sumscore");
            String maxscore = resultSet.getString("maxscore");
            String avgscore = resultSet.getString("avgscore");
            String minscore = resultSet.getString("minscore");
            System.out.println(subjectid+":"+subjectname+":"+sumscore+":"+maxscore+":"+minscore+":"+avgscore);
        }
    }

    public final Random r = new Random();

    public void testQuery() throws Exception{
        String str = "select $1,$2 from o_m_t_score s left join o_m_t_subject j on s.subject_id = j.id left join o_m_t_student d on s.user_id = d.id where 1=1 $5 group by $3";
        List<String> p1 = new ArrayList();
        p1.add("s.subject_id");
        p1.add("j.subject_name");
        p1.add("s.user_id");
        p1.add("d.user_name");
        p1.add("d.province");
        p1.add("d.city");
        p1.add("d.sex");

        List<String> p2 = new ArrayList();
        p2.add("sum(s.score) as sumscore");
        p2.add("max(s.score) as maxscore");
        p2.add("min(s.score) as minscore");
        p2.add("avg(s.score) as avgscore");
        p2.add("count(s.score) as countscore");

        List<String> p3 = new ArrayList();
        p3.add("subject_id");
        p3.add("subject_name");
        p3.add("user_id");
        p3.add("user_name");
        p3.add("province");
        p3.add("city");
        p3.add("sex");

        List<String> p4 = new ArrayList();
        p4.add("sumscore");
        p4.add("maxscore");
        p4.add("minscore");
        p4.add("avgscore");
        p4.add("countscore");

        List<String> p5 = new ArrayList<>();
        p5.add(" and j.subject_name = '英语'");
        p5.add(" and d.city = '唐山'");

        Set<String> result = new HashSet<>();
        Set<String> set1 = new HashSet<>();

        int lenp = r.nextInt(7)+1;
        while(lenp > 0){
            int index = r.nextInt(7);
            set1.add(p1.get(index));
            result.add(p3.get(index));
            lenp --;
        }

        Set<String> set2 = new HashSet<>();
        lenp = r.nextInt(5)+1;
        while(lenp > 0){
            int index = r.nextInt(5);
            set2.add(p2.get(index));
            result.add(p4.get(index));
            lenp --;
        }
        Set<String> set5 = new HashSet<>();
        lenp = r.nextInt(2)+1;
        while (lenp > 0){
            int index = r.nextInt(2);
            set5.add(p5.get(index));
            lenp--;
        }

        String s1 = StringUtils.join(set1.toArray(),",");
        String s2 = StringUtils.join(set2.toArray(),",");
        String s5 = StringUtils.join(set5.toArray()," ");

        str = str.replace("$1",s1);
        str = str.replace("$2",s2);
        str = str.replace("$3",s1);
        str = str.replace("$5",s5);

        System.out.println("sql:");
        System.out.println(str);

        PreparedStatement state = conn.prepareStatement(str);
        ResultSet resultSet = state.executeQuery();
        while(resultSet.next()){
            for(String key : result){
                System.out.print(resultSet.getString(key)+"\t");
            }
            System.out.println();
        }
    }
}
